package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.FileType;
import com.kaishengit.entity.Project;
import com.kaishengit.entity.User;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;



/*
 * 
 * 

SELECT tf.id,tf.name,tf.createtime,t_user_id,t_project_id, tu.name AS username,tu.password,tu.pic,tp.name AS pname,tp.desc,tp.createtime AS pcreatetime FROM filetype AS tf
LEFT JOIN t_user AS tu ON tf.t_user_id=tu.id
LEFT JOIN t_project AS tp ON tf.t_project_id=tp.id
WHERE  tf.t_project_id='p1';
 * */
public class FileTypeDao {
		private DBHelp<FileType> db=new DBHelp<FileType>();
	
	public List<FileType> findByProjectId(String ProjectId){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT tf.id,tf.name,tf.createtime,t_user_id,t_project_id, tu.name AS username,tu.password,tu.pic,tp.name AS pname,tp.desc,tp.createtime AS pcreatetime ");
		sql.append("FROM filetype AS tf ");
		sql.append("LEFT JOIN t_user AS tu ON tf.t_user_id=tu.id ");
		sql.append("LEFT JOIN t_project AS tp ON tf.t_project_id=tp.id ");
		sql.append("WHERE tf.t_project_id=? ");
		return db.executeQueryForList(sql.toString(), new FileTypeRowmapper(),ProjectId);

	}
	public FileType findById(String ProjectId){
			StringBuilder sql=new StringBuilder();
			sql.append("SELECT tf.id,tf.name,tf.createtime,t_user_id,t_project_id, tu.name AS username,tu.password,tu.pic,tp.name AS pname,tp.desc,tp.createtime AS pcreatetime ");
			sql.append("FROM filetype AS tf ");
			sql.append("LEFT JOIN t_user AS tu ON tf.t_user_id=tu.id ");
			sql.append("LEFT JOIN t_project AS tp ON tf.t_project_id=tp.id ");
			sql.append("WHERE tf.t_project_id=? ");		
			return db.executeQueryForObject(sql.toString(), new FileTypeRowmapper(),ProjectId);
		}
	
	
	public void save(FileType ft){
		final String sql="INSERT INTO filetype(id,`name`,createtime,t_user_id,t_project_id)VALUES(?,?,?,?,?)";
		 db.executeSQL(sql,ft.getId(),ft.getName(),ft.getCreatetime(),ft.getUserid(),ft.getProjectid());
		
		
	}
	public void Del(String id){
		String sql=" DELETE  FROM filetype WHERE id=? ";
		db.executeSQL(sql, id);
		
		
	}
	public class FileTypeRowmapper implements RowMapper<FileType> {

		public FileType mapperRow(ResultSet rs) throws SQLException {
			FileType filetype=new FileType();
			
			filetype.setId(rs.getString("id"));
			filetype.setName(rs.getString("name"));
			filetype.setCreatetime(rs.getString("createtime"));
			

			User user=new User();
			user.setId(rs.getString("t_user_id"));
			user.setName(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setPic(rs.getString("pic"));
			
			filetype.setUser(user);
			filetype.setUserid(rs.getString("t_user_id"));
		
			Project project=new Project();
			project.setId("t_project_id");
			project.setName(rs.getString("pname"));
			project.setDesc(rs.getString("desc"));
			project.setCreatetime(rs.getString("pcreatetime"));
			
			filetype.setProject(project);
			filetype.setProjectid(rs.getString("t_project_id"));
				return filetype;
		}

	}
}
